import pymysql

class DBUtil:
    # 定义类属性
    conn = None

    # 封装链接数据库操作,调用方不关心是如何链接或者关闭数据库的，隐藏掉
    @classmethod
    def __connect_db(cls):
        cls.conn = pymysql.connect(host="211.103.136.244", user="student", password="iHRM_student_2022",
                                   database="ihrm", port=7061, charset="utf8")
        return cls.conn

    # 关闭数据库操作，变成私有方法
    @classmethod
    def __close_db(cls):
        cls.conn.close()
        pass

    # 封装查询操作
    @classmethod
    def query_db(cls, sql):
        cursor = None
        result = None
        try:
            # 创建数据库的链接
            cls.conn = cls.__connect_db()
            # 获取游标
            cursor = cls.conn.cursor()
            # 执行sql 语句，把查询结果return
            cursor.execute(sql)
            result = cursor.fetchone()
        except Exception as err:
            print("读取数据库报错:", err)
        finally:
            cursor.close()
            cls.__close_db()
            return result

    # 封装增删改操作
    @classmethod
    def update_db(cls, sql):
        cursor =None
        try:
            cls.conn = cls.__connect_db()
            # 获取游标
            cursor = cls.conn.cursor()
            # 执行sql 语句，打印一下影响数据库的行数
            cursor.execute(sql)
            row = cls.conn.affected_rows()
            print("数据库影响的行数：", row)
            # 事务的提交
            cls.conn.commit()
        except Exception as err:
            print("修改数据库报错：",err)
            #事务的回滚
            cls.conn.rollback()
        finally:
            # 关闭游标
            cursor.close()
            # 关闭数据库链接
            cls.__close_db()


# if __name__ == '__main__':
#     insert_sql="INSERT into t_book (id,title,`read`,comment,pub_date)  values(111112,'python',50,0,'2020-01-01');"
#     DBUtil.update_db(insert_sql)
#     update_sql="UPDATE t_book set comment=200 where id =111112;"
#     DBUtil.update_db(update_sql)
#     del_sql="DELETE from t_book WHERE id =111112;"
#     DBUtil.update_db(del_sql)
#     select_sql="SELECT * from t_book tb WHERE id=111112;"
#     DBUtil.query_db(select_sql)
#     pass
#
#     pass
